﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using PMS.ENT;
using System.Data.SqlClient;
using PMS.Utilities;
using System.Configuration;


namespace PMS.DAL
{
    public class CommentDAL
    {
        #region Common
        private static CommentENT CreateCommentFromReader(IDataReader reader)
        {
            CommentENT item = new CommentENT();
            try
            {

                if (!reader.IsDBNull(reader.GetOrdinal(CommentColumns.CommentID.ToString()))) item.CommentID = Utility.IntParse(reader[CommentColumns.CommentID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(CommentColumns.OwnerID.ToString()))) item.OwnerID = Utility.IntParse(reader[CommentColumns.OwnerID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(CommentColumns.ScheduleStatementID.ToString()))) item.ScheduleStatementID = Utility.IntParse(reader[CommentColumns.ScheduleStatementID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(CommentColumns.Text.ToString()))) item.Text = Utility.StringParse(reader[CommentColumns.Text.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(CommentColumns.CreatedBy.ToString()))) item.CreatedBy = Utility.IntParse(reader[CommentColumns.CreatedBy.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(CommentColumns.UpdateBy.ToString()))) item.UpdateBy = Utility.IntParse(reader[CommentColumns.UpdateBy.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(CommentColumns.CreatedDate.ToString()))) item.CreatedDate = Utility.DateTimeParse(reader[CommentColumns.CreatedDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(CommentColumns.UpdateDate.ToString()))) item.UpdateDate = Utility.DateTimeParse(reader[CommentColumns.UpdateDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(CommentColumns.Isdeleted.ToString()))) item.Isdeleted = Utility.IntParse(reader[CommentColumns.Isdeleted.ToString()]);

                DataTable dt = reader.GetSchemaTable();
                if(Utility.FieldExist(dt,CommentColumns.Owner.ToString()))
                {
                    if (!reader.IsDBNull(reader.GetOrdinal(CommentColumns.Owner.ToString()))) item.Owner = Utility.StringParse(reader[CommentColumns.Owner.ToString()]);

                }

                item.StrCreatedDate = item.CreatedDate.ToString("dd/MM/yyyy");
            }
            catch
            {
                throw;
            }
            return item;
        }
        #endregion

        #region Get
        public static CommentENT GetCommentByID(int commentID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procCommentGetByID.ToString());
                database.AddInParameter(dbCommand, "@CommentID", DbType.Int32, commentID);

                CommentENT comment = new CommentENT();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        comment = CreateCommentFromReader(reader);
                    }
                    reader.Close();
                }

                return comment;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetCommentByID");
            }
        }

        public static CommentCollection GetCommentList(string commentName, int projectID, int scheduleID, int userID, string status, string orderBy, string orderDirection, int page, int pageSize, out int totalRecords)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procCommentGetList.ToString());
                CommentCollection listComment = new CommentCollection();
                database.AddInParameter(dbCommand, "@CommentName", DbType.String, commentName);
                database.AddInParameter(dbCommand, "@ProjectID", DbType.Int32, projectID);
                database.AddInParameter(dbCommand, "@ScheduleID", DbType.Int32, scheduleID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);
                database.AddInParameter(dbCommand, "@Status", DbType.String, status);
                database.AddInParameter(dbCommand, "@OrderBy", DbType.AnsiString, orderBy);
                database.AddInParameter(dbCommand, "@OrderDirection", DbType.AnsiString, orderDirection);
                database.AddInParameter(dbCommand, "@Page", DbType.Int32, page);
                database.AddInParameter(dbCommand, "@PageSize", DbType.Int32, pageSize);
                database.AddOutParameter(dbCommand, "@TotalRecords", DbType.Int32, 0);

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listComment.Add(CreateCommentFromReader(reader));
                    }
                    reader.Close();
                }

                totalRecords = (int)database.GetParameterValue(dbCommand, "@TotalRecords");

                return listComment;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetCommentList");
            }
        }

        public static CommentCollection GetCommentListByStatementID(int statementID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procCommentGetListByStatementID.ToString());
                CommentCollection listComment = new CommentCollection();
                database.AddInParameter(dbCommand, "@StatementID", DbType.String, statementID);

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listComment.Add(CreateCommentFromReader(reader));
                    }
                    reader.Close();
                }


                return listComment;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetCommentList");
            }
        }

        public static CommentCollection GetCommentListForCMB(int userID, int projectID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procCommentGetListForCMB.ToString());
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);
                database.AddInParameter(dbCommand, "@ProjectID", DbType.Int32, projectID);

                CommentCollection listComment = new CommentCollection();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listComment.Add(CreateCommentFromReader(reader));
                    }
                    reader.Close();
                }

                return listComment;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetCommentListForCMB");
            }
        }

        #endregion

        #region Insert, Update, Delete

        public static int InsertComment(CommentENT comment)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procCommentInsert.ToString());
                int commentID = 0;
                database.AddOutParameter(dbCommand, "@CommentID", DbType.Int32, comment.CommentID);
                database.AddInParameter(dbCommand, "@OwnerID", DbType.Int32, comment.OwnerID);
                database.AddInParameter(dbCommand, "@ScheduleStatementID", DbType.Int32, comment.ScheduleStatementID);
                database.AddInParameter(dbCommand, "@Text", DbType.String, comment.Text);
                database.AddInParameter(dbCommand, "@CreatedBy", DbType.Int32, comment.CreatedBy);
                database.AddInParameter(dbCommand, "@UpdateBy", DbType.Int32, comment.UpdateBy);
                database.AddInParameter(dbCommand, "@Isdeleted", DbType.Int32, comment.Isdeleted);

                database.ExecuteNonQuery(dbCommand);
                commentID = Utility.IntParse(database.GetParameterValue(dbCommand, "@CommentID"));
                return commentID;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_InsertComment");
            }
        }



        public static int UpdateComment(CommentENT comment)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procCommentUpdate.ToString());

                database.AddInParameter(dbCommand, "@CommentID", DbType.Int32, comment.CommentID);
                database.AddInParameter(dbCommand, "@OwnerID", DbType.Int32, comment.OwnerID);
                database.AddInParameter(dbCommand, "@ScheduleStatementID", DbType.Int32, comment.ScheduleStatementID);
                database.AddInParameter(dbCommand, "@Text", DbType.String, comment.Text);
                database.AddInParameter(dbCommand, "@UpdateBy", DbType.Int32, comment.UpdateBy);
                database.AddInParameter(dbCommand, "@Isdeleted", DbType.Int32, comment.Isdeleted);

                database.ExecuteNonQuery(dbCommand);

                int commentID = Utility.IntParse(database.GetParameterValue(dbCommand, "@CommentID"));
                return commentID;

            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_InsertComment");
            }
        }



        public static int DeleteComment(string commentID, int userID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procCommentDelete.ToString());
                database.AddInParameter(dbCommand, "@CommentID", DbType.String, commentID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);

                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    return 1;
                }
                return 0;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_InsertComment");
            }
        }


        public static int ConfirmComment(string commentID, int userID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procCommentConfirm.ToString());
                database.AddInParameter(dbCommand, "@CommentID", DbType.String, commentID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);

                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    return 1;
                }
                return 0;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_ConfirmComment");
            }
        }
        #endregion


    }
}
